﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;

namespace LdExpress
{
    public class Carrs
    {
        public long ID = -1;
        public string Name = "";
        public string Code = "";
        public string Rank = "";
        public string ServiceArea = "";
        public string Description = "";
        public int Type = 0;
        public int Sort = 0;
        public DateTime LastUpdate = DateTime.Now;
        public string BizName = "";
        public string FullName = "";
        public string InvoicingName = "";
        public string Address = "";
        public string MST = "";
        public long LocationID = 0;
        public Carrs()
        {
        }

        public Carrs(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_carrs";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                             "`Name`" +
                             ",`Code`" +
                             ",`Rank`" +
                             ",`ServiceArea`" +
                             ",`Description`" +
                             ",`Type`" +
                             ",`Sort`" +
                             ",`LastUpdate`" +
                             ",`BizName`" +
                             ",`FullName`" +
                             ",`InvoicingName`" +
                             ",`Address`" +
                             ",`MST`" +
                             ",`LocationID`" +
                             ") " +
                         " values(" +
                             "'" + DBModule.RefineString(Name) + "'" +
                             "," + "'" + DBModule.RefineString(Code) + "'" +
                             "," + "'" + DBModule.RefineString(Rank) + "'" +
                             "," + "'" + DBModule.RefineString(ServiceArea) + "'" +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + Type.ToString() +
                             "," + Sort.ToString() +
                             "," + DBModule.RefineDatetime(LastUpdate) +
                             "," + "'" + DBModule.RefineString(BizName) + "'" +
                             "," + "'" + DBModule.RefineString(FullName) + "'" +
                             "," + "'" + DBModule.RefineString(InvoicingName) + "'" +
                             "," + "'" + DBModule.RefineString(Address) + "'" +
                             "," + "'" + DBModule.RefineString(MST) + "'" +
                             "," + LocationID.ToString() +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("Carrs", "add", cn, trans);
                //GV.Response.Write(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                            "Name='" + DBModule.RefineString(Name) + "'" +
                            ",Code='" + DBModule.RefineString(Code) + "'" +
                            ",Rank='" + DBModule.RefineString(Rank) + "'" +
                            ",ServiceArea='" + DBModule.RefineString(ServiceArea) + "'" +
                            ",Description='" + DBModule.RefineString(Description) + "'" +
                            ",Type = " + Type.ToString() +
                            ",Sort = " + Sort.ToString() +
                            ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                            ",BizName='" + DBModule.RefineString(BizName) + "'" +
                            ",FullName='" + DBModule.RefineString(FullName) + "'" +
                            ",InvoicingName='" + DBModule.RefineString(InvoicingName) + "'" +
                            ",Address='" + DBModule.RefineString(Address) + "'" +
                            ",MST='" + DBModule.RefineString(MST) + "'" +
                            ",LocationID = " + LocationID.ToString() +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("Carrs", "edit", cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_carrs WHERE ID = " + this.ID.ToString();

            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("Carrs", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_carrs WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Name")) Name = dr["Name"].ToString();
                if (!dr.IsNull("Code")) Code = dr["Code"].ToString();
                if (!dr.IsNull("Rank")) Rank = dr["Rank"].ToString();
                if (!dr.IsNull("ServiceArea")) ServiceArea = dr["ServiceArea"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("Type")) Type = int.Parse(dr["Type"].ToString());
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
                if (!dr.IsNull("BizName")) BizName = dr["BizName"].ToString();
                if (!dr.IsNull("FullName")) FullName = dr["FullName"].ToString();
                if (!dr.IsNull("InvoicingName")) InvoicingName = dr["InvoicingName"].ToString();
                if (!dr.IsNull("Address")) Address = dr["Address"].ToString();
                if (!dr.IsNull("MST")) MST = dr["MST"].ToString();
                if (!dr.IsNull("LocationID")) LocationID = long.Parse(dr["LocationID"].ToString());
            }
        }

        public void Load(string inputName, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_carrs WHERE Name = '" + DBModule.RefineString(inputName) + "' limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Name")) Name = dr["Name"].ToString();
                if (!dr.IsNull("Code")) Code = dr["Code"].ToString();
                if (!dr.IsNull("Rank")) Rank = dr["Rank"].ToString();
                if (!dr.IsNull("ServiceArea")) ServiceArea = dr["ServiceArea"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("Type")) Type = int.Parse(dr["Type"].ToString());
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
                if (!dr.IsNull("BizName")) BizName = dr["BizName"].ToString();
                if (!dr.IsNull("FullName")) FullName = dr["FullName"].ToString();
                if (!dr.IsNull("InvoicingName")) InvoicingName = dr["InvoicingName"].ToString();
                if (!dr.IsNull("Address")) Address = dr["Address"].ToString();
                if (!dr.IsNull("MST")) MST = dr["MST"].ToString();
                if (!dr.IsNull("LocationID")) LocationID = long.Parse(dr["LocationID"].ToString());
            }
        }
        public static void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select tbl_carrs.*, TBLocation.Name as LocationName from tbl_carrs " +
            " left join tbl_Location TBLocation on tbl_carrs.LocationID = TBLocation.ID " +
            " where 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                if (strIDs != "All")
                    strSQL += strIDs;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_carrs where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public static string GetUniqueCode(long ID, string inputcode, MySqlConnection con, MySqlTransaction trans)
        {
            string retVal = inputcode;
            string sqlCommand = "";
            int count = 1;
            while (true)
            {
                sqlCommand = "Select * from tbl_carrs where ID <> " + ID.ToString() + " And code='" + DBModule.RefineString(retVal) + "' limit 0,1";

                DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
                if (ds.Tables[0].Rows.Count == 0)
                {
                    break;
                }
                else
                {
                    retVal = inputcode + "-" + count;
                    count++;
                }
            }
            return retVal;
        }
        public static bool GetCheckCode(long ID, string inputcode, MySqlConnection con, MySqlTransaction trans)
        {
            bool bCode = false;
            string retVal = inputcode;
            string sqlCommand = "";
            int count = 1;

            sqlCommand = "Select * from tbl_carrs where ID <> " + ID.ToString() + " And code='" + DBModule.RefineString(retVal) + "' limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds.Tables[0].Rows.Count > 0)
            {
                bCode = true;
            }
            return bCode;
        }
        public static long GetCarrierIDFromName(string name)
        {
            string sqlCommand = "select ID from tbl_carrs where name ='" + DBModule.RefineString(name) + "' limit 0,1;";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
            if (ds.Tables[0].Rows.Count > 0)
                return long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
            else
            {
                return 0;
            }
        }

        public static string GetCarrNameFromID(long ID)
        {
            string sqlCommand = "select name from tbl_carrs where ID =" + ID + " limit 0,1;";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0]["name"].ToString();
            else
            {
                return "";
            }
        }
    }
}

